import pandas as pd
import sqlite3
from pathlib import Path
from dotenv import load_dotenv
import os


class ExcelHandler:
    def __init__(self, excel_path: str = "data_sales.xls"):
        """
        初始化，加载Excel数据
        """
        load_dotenv()
        project_root = os.getenv("PROJECT_ROOT")

        self.project_root = Path(project_root)
        self.excel_path = self.project_root / excel_path

    def _create_new_connection(self) -> sqlite3.Connection:
        """
        将Excel数据加载到内存的SQLite数据库
        """
        if not self.excel_path.exists():
            raise FileNotFoundError(f"数据文件不存在：{self.excel_path}")

        df = pd.read_excel(
            self.excel_path,
            sheet_name="订单",
            parse_dates=['订单日期', '发货日期'],
            date_format='%Y-%m-%d'
        )
        df['订单日期'] = pd.to_datetime(df['订单日期'], errors='coerce')
        df['发货日期'] = pd.to_datetime(df['发货日期'], errors='coerce')

        df['订单日期'] = df['订单日期'].dt.strftime('%Y-%m-%d')
        df['发货日期'] = df['发货日期'].dt.strftime('%Y-%m-%d')

        conn = sqlite3.connect(":memory:")
        df.to_sql("sales", conn, index=False, if_exists="replace")

        return conn

    def execute_sql(self, sql: str) -> tuple[pd.DataFrame | None, str]:
        """
        执行SQL查询，返回DataFrame和状态信息
        """
        conn = None
        try:
            conn = self._create_new_connection()
            result_df = pd.read_sql(sql, conn)
            return result_df, "查询成功！"
        except Exception as e:
            return None, f"查询失败：{str(e)}"
        finally:
            if conn:
                conn.close()


if __name__ == "__main__":
    processor = ExcelHandler()
    test_sql = "SELECT * FROM sales WHERE 订单日期 = '2018-04-27' LIMIT 5"
    result, msg = processor.execute_sql(test_sql)
    print("查询结果：")
    print(result if result is not None else msg)
